package cmcc.dbi.execJdbcUtil;

import cmcc.dbi.dto.DbQueryDTO;
import cmcc.dbi.entity.InspectionItemRecord;
import cmcc.dbi.exception.ExecutEngineExcption;
import cmcc.dbi.execEnum.DbTypeOfDbNameEnum;
import cmcc.dbi.execEnum.DbTypeOfDriverEnum;
import cmcc.dbi.execEnum.DbTypeOfUrlEnum;
import cmcc.dbi.execUtil.AESEncryptionUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Slf4j
public class getRes4AllDbUtil {

    final static String GETDATNAME = "select datname from pg_database where datname not in ('template0','template1');";

    public JSONArray getTargetAlldbResult(List<InspectionItemRecord> scriptsLists, DbQueryDTO dbQueryDTO) throws ClassNotFoundException, SQLException {
        // 用于保存所有巡检项结果
        JSONArray queryAllResult = new JSONArray();
        try {
            List<String> datNames = getDatName(dbQueryDTO);
            for (String datName : datNames) {
                //按照 datName 进行连接并获得结果
                JSONArray datNameResult = getResult(datName, scriptsLists, dbQueryDTO);
                //System.out.println(datNameResult);
                // 把 结果 按照 巡检项 进行组合
                for (int i = 0; i < datNameResult.size(); i++) {
                    JSONObject itemResult = datNameResult.getJSONObject(i);
                    Long itemId = itemResult.getLong("item_id");
                    JSONArray jdbcResult = itemResult.getJSONArray("jdbc_result");
                    boolean found = false;
                    for (int j = 0; j < queryAllResult.size(); j++) {
                        JSONObject existingItem = queryAllResult.getJSONObject(j);
                        if (existingItem.getLong("item_id").equals(itemId)) {
                            existingItem.getJSONArray("jdbc_result").addAll(jdbcResult);
                            found = true;
                            break;
                        }
                    }
                    if (!found) {
                        queryAllResult.add(itemResult);
                    }
                }
            }
        } catch (ClassNotFoundException e) {
            throw new ClassNotFoundException();
        } catch (SQLException e) {
            throw new SQLException(e);
        }
        return queryAllResult;
    }

    private List<String> getDatName(DbQueryDTO dbQueryDTO) throws ClassNotFoundException {
        List<String> datNames = new ArrayList<>();
        // 加载JDBC驱动
        String dbDriver = DbTypeOfDriverEnum.match(dbQueryDTO.getDbType()).getDbDriver();
        Class.forName(dbDriver);
        // 获取默认库名称
        String theDatName = DbTypeOfDbNameEnum.match(dbQueryDTO.getDbType()).getDbName();
        // 替换默认的url构建连接
        String url = DbTypeOfUrlEnum.match(dbQueryDTO.getDbType()).getDbUrl().replace("DBIP", dbQueryDTO.getDbIP())
                .replace("DBPORT", dbQueryDTO.getDbPort()).replace("DBNAME", theDatName);

        String pass = AESEncryptionUtil.decrypt(dbQueryDTO.getDbPass());

        try (Connection con = DriverManager.getConnection(url, dbQueryDTO.getDbUser(), pass)) {
            if (con != null) {
                log.info("所有库巡检，内置脚本查询 datname 信息: " + dbQueryDTO.getDbIP() + " - " + dbQueryDTO.getDbPort() + " - "
                        + dbQueryDTO.getDbUser() + " - " + dbQueryDTO.getDbPass() + " - " + theDatName);

                //log.info("成功连接到默认数据库！");
                try (Statement stmt = con.createStatement();
                     ResultSet resultSet = stmt.executeQuery(GETDATNAME)) {
                    while (resultSet.next()) {
                        String columnName = resultSet.getMetaData().getColumnLabel(1);
                        String values = resultSet.getString(columnName);
                        datNames.add(values);
                        log.debug("数据库表：{} -- {}", columnName, values);
                    }
                    //log.info("查询数据库名列表成功");
                } catch (SQLException e) {
                    log.error("查询数据库表sql语句，执行失败", e);
                    throw new ExecutEngineExcption("查询数据库表sql语句，执行失败，" + e.getMessage());
                }
            }
        } catch (SQLException e) {
            log.error("查询数据库表sql语句，建连失败", e);
            throw new ExecutEngineExcption("查询数据库表sql语句，建连失败," + e.getMessage());
        }
        return datNames;
    }

    private JSONArray getResult(String dbName, List<InspectionItemRecord> scriptsLists, DbQueryDTO dbQueryDTO) throws ClassNotFoundException, SQLException {
        // 加载Kingbase JDBC驱动
        //加载驱动
        String dbDriver = DbTypeOfDriverEnum.match(dbQueryDTO.getDbType()).getDbDriver();
        Class.forName(dbDriver);
        String url = DbTypeOfUrlEnum.match(dbQueryDTO.getDbType()).getDbUrl().replace("DBIP", dbQueryDTO.getDbIP())
                .replace("DBPORT", dbQueryDTO.getDbPort()).replace("DBNAME", dbName);

        String pass = AESEncryptionUtil.decrypt(dbQueryDTO.getDbPass());

        // 用于保存所有巡检项结果
        JSONArray queryAllResult = new JSONArray();

        try (Connection con = DriverManager.getConnection(url, dbQueryDTO.getDbUser(), pass)) {
            if (con != null) {
                log.info("所有库巡检，连接信息: " + dbQueryDTO.getDbIP() + " - " + dbQueryDTO.getDbPort() + " - "
                        + dbQueryDTO.getDbUser() + " - " + dbQueryDTO.getDbPass() + " - " + dbName);

                // 用于执行静态SQL语句并返回其产生的结果的对象
                Statement stmt = con.createStatement();

                // 分别执行各个脚本
                for (InspectionItemRecord scriptsList : scriptsLists) {
                    // 保存单个巡检项对应的结果
                    List<Map<String, Object>> lisTmp = new ArrayList<>();
                    // 获取当前巡检项的脚本
                    String SqlCom = scriptsList.getScript();
                    JSONObject querySingleResult = new JSONObject();

                    // 执行某个脚本
                    try {
                        boolean hasResult = stmt.execute(SqlCom);
                        if (hasResult) {
                            // 有执行结果
                            //log.info("The SqlCom execute success");
                            ResultSet resultSet = stmt.getResultSet();
                            // 多个key-value 代表 一列多行 ，实际只有一行
                            Map<String, Object> tmp = new HashMap<>();
                            while (resultSet.next()) {
                                // 获取第一列表头
                                String columnName = resultSet.getMetaData().getColumnLabel(1);
                                // 获取结果
                                String values = resultSet.getString(columnName);
                                // 多行--正常只有一行
                                tmp.put(columnName, values);
                                tmp.put("db_name", dbName);
                                log.info("所有库巡检，当前库为："+dbName+",sql执行成功，执行结果：" + columnName + " -- " + values);
                            }
                            // 某一巡检项对应的结果，一列多行（应是一列一行） [{key-value},{key-value}]
                            lisTmp.add(tmp);
                        } else {
                            log.info("所有库巡检sql执行成功，当前库为："+dbName+",但没有执行结果");
                        }
                    } catch (SQLException e) {
                        //log.info("The SqlCom execute failed");
                        log.debug("所有库巡检，当前库为："+dbName+",SQL执行失败，错误信息：" + e.getMessage());
                        // 记录错误信息
                        Map<String, Object> tmp = new HashMap<>();
                        tmp.put("error_info", String.valueOf(e));
                        lisTmp.add(tmp);
                    }
                    querySingleResult.put("item_id", scriptsList.getItemId());
                    querySingleResult.put("jdbc_result", lisTmp);
                    queryAllResult.add(querySingleResult);
                }
            }
        }
        return queryAllResult;
    }



}
